using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Reflection;
using Cobalt75.GreyMatter;
using GMID = System.UInt64;
using System.Data.SqlClient;

namespace Cobalt75.GreyMatter.SQLExpress
{
    public class SQLExpressGMCommunicator : GMCommunicator
    {
        public SQLExpressGMCommunicator(string databaseFilePath,string database)
            : base(database)
        {
            //Init("Persist Security Info=False;database=" + pDatabase + ";server=localhost;user id=" + pDatabase + ";password=" + pDatabase + ";");
            Init("Data Source=.\\SQLExpress;AttachDbFilename=" + databaseFilePath + "\\" + pDatabase + ".mdf;Integrated Security=True;Connect Timeout=30;User Instance=False;");

        }
        public SQLExpressGMCommunicator(string databaseFilePath,string database, string server)
            : base(database)
        {
            Init("Data Source=" + server + ";AttachDbFilename=" + databaseFilePath + "\\" + pDatabase + ".mdf;Integrated Security=True;Connect Timeout=30;User Instance=False;");

        }
        public SQLExpressGMCommunicator(string databaseFilePath,string database, string server,string userName,string password)
            : base(database)
        {
            Init("Data Source=" + server + ";AttachDbFilename=" + databaseFilePath + "\\" + pDatabase + ".mdf;Integrated Security=True;Connect Timeout=30;User Instance=False;");

        }

        public override string ParameterPrefix
        {
            get
            {
                return ("?");
            }
        }
        private void Init(string _ConnectionString)
        {
            //_Database = "gmtest";
            pConnection = new SqlConnection();
            pConnection.ConnectionString = _ConnectionString;
            
            pConnection.Open();
        }
        ~SQLExpressGMCommunicator()
        {
            CloseAndDispose();
        }
        public void CloseAndDispose()
        {
            pConnection.Close();
            pConnection.Dispose();
        }

        public override IDataParameter CreateParameter(string paraName, object paraValue)
        {
            return new SqlParameter(paraName,paraValue);

        }
        protected override GMID GetLastInsertID(IDbTransaction transaction)
        {
            IDbCommand Command = pConnection.CreateCommand();
            Command.Transaction = transaction;

            Command.CommandText = "SELECT LAST_INSERT_ID()";

            return (GMID)Convert.ChangeType(Command.ExecuteScalar().ToString(),typeof(GMID));
        }
        public override object DBToCLR(object obj, GMColumnAttribute attr)
        {
            if (attr == null)
            {
                return obj; //For ID lookup
            }
            object v = obj;
            Type t = attr.CLRType;
            if (attr.IsPrimary)
            {
                return v;
            }
            else if (attr.CLRType.IsGenericType)
            {
                /*
                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    return (((GMTable)v).ID);
                }*/
                return v;

            }
            else if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                else if (t == typeof(Int16))
                {

                    return v;
                }
                else if (t == typeof(Int32))
                {

                    return v;
                }
                else if (t == typeof(Int64))
                {

                    return v;
                }
                else if (t == typeof(UInt16))
                {

                    return v;
                }
                else if (t == typeof(UInt32))
                {

                    return v;
                }
                else if (t == typeof(UInt64))
                {

                    return v;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    return new DateTime((long)v);
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                else if (t == typeof(char))
                {

                    return v;
                }
                else if (t == typeof(decimal))
                {

                    return v;
                }
                else if (t == typeof(bool))
                {

                    return v;
                }
                else if (t == typeof(Byte))
                {

                    return v;
                }
                else if (t == typeof(float))
                {

                    return v;
                }
                else if (t == typeof(double))
                {

                    return v;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        public override object CLRToDB(object obj, GMColumnAttribute attr)
        {
            object v = attr.CLRField.GetValue(obj);
            Type t = attr.CLRType;
            if (attr.IsPrimary)
            {
                return v;
            }
            else if (attr.CLRType.IsGenericType)
            {
                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    //return (((GMRelation<GMTable>)v).Value.ID);
                    return ((IGMRelation)v).GetID();
                }
                return v;

            }
            else if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                else if (t == typeof(Int16))
                {

                    return v;
                }
                else if (t == typeof(Int32))
                {

                    return v;
                }
                else if (t == typeof(Int64))
                {

                    return v;
                }
                else if (t == typeof(UInt16))
                {

                    return v;
                }
                else if (t == typeof(UInt32))
                {

                    return v;
                }
                else if (t == typeof(UInt64))
                {

                    return v;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    return ((DateTime)v).Ticks;
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                else if (t == typeof(char))
                {

                    return v;
                }
                else if (t == typeof(decimal))
                {

                    return v;
                }
                else if (t == typeof(bool))
                {

                    return v;
                }
                else if (t == typeof(Byte))
                {

                    return v;
                }
                else if (t == typeof(float))
                {

                    return v;
                }
                else if (t == typeof(double))
                {

                    return v;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        protected override void SetDatabaseSchemaField(Type t, ref GMColumnAttribute attr)
        {
            if (attr.IsPrimary)
            {
                attr.SetDatabase("BIGINT(20)","0");
                return;
            }
            else if (attr.CLRType.IsGenericType)
            {
                Type ta = attr.CLRType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    attr.SetDatabase("BIGINT(20)", "0");
                    return;
                }
                attr.SetDatabase("?", "?");
                return;

            }
            else if (t.IsArray && t.IsValueType)
            {
                attr.SetDatabase("?", "?");
                return;
            }
            else if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    attr.SetDatabase("BIGINT(20)", "0");
                    return;
                }
                else if (t == typeof(Int16))
                {

                    attr.SetDatabase("SMALLINT", "0");
                    return;
                }
                else if (t == typeof(Int32))
                {

                    attr.SetDatabase("INT", "0");
                    return;
                }
                else if (t == typeof(Int64))
                {

                    attr.SetDatabase("BIGINT", "0");
                    return;
                }
                else if (t == typeof(UInt16))
                {

                    attr.SetDatabase("SMALLINT", "0");
                    return;
                }
                else if (t == typeof(UInt32))
                {

                    attr.SetDatabase("INT", "0");
                    return;
                }
                else if (t == typeof(UInt64))
                {

                    attr.SetDatabase("BIGINT", "0");
                    return;
                }
                else if (t == typeof(DateTime) && attr.Precision)
                {

                    attr.SetDatabase("BIGINT(20)", "0");
                    return;
                }
                else if (t == typeof(DateTime) && !attr.Precision)
                {

                    attr.SetDatabase("DATETIME", "0");
                    return;
                }
                else if (t == typeof(char))
                {

                    attr.SetDatabase("SMALLTEXT", "");
                    return;
                }
                else if (t == typeof(decimal))
                {

                    attr.SetDatabase("DECIMAL(65,30)", "0");
                    return;
                }
                else if (t == typeof(bool))
                {

                    attr.SetDatabase("TINYINT(1)", "0");
                    return;
                }
                else if (t == typeof(Byte))
                {

                    attr.SetDatabase("TINYINT", "0");
                    return;
                }
                else if (t == typeof(float))
                {

                    attr.SetDatabase("FLOAT", "0");
                    return;
                }
                else if (t == typeof(double))
                {

                    attr.SetDatabase("DOUBLE", "0");
                    return;
                }
            }
            else if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    attr.SetDatabase("SMALLTEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Medium)
                {

                    attr.SetDatabase("MEDIUMTEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Large)
                {

                    attr.SetDatabase("LONGTEXT", null);
                    return;
                }
                else if (attr.LengthType == GMColumnLength.Undefined)
                {

                    attr.SetDatabase("TEXT", null);
                    return;
                }
                else
                {
                    attr.SetDatabase("VARCHAR(" + attr.ColumnLength + ")", "");
                    
                    return;
                }
            }
            attr.SetDatabase("BLOB","");
            return;
        }

        public override string TempIDTableCreationSQL()
        {
            return (String.Format(@"
DROP TABLE IF EXISTS {0}.tbl_ids;
CREATE TABLE  {0}.tbl_ids (
  col bigint(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", this.Database));
        }
        public override void CreateDBSchema()
        {
            Console.WriteLine("DB Schema (designed for Sql5+)");
            //First create the main tables
            //(In order of priority)
            SortedDictionary<int, GMTableAttribute> SORTtabAttrs = new SortedDictionary<int, GMTableAttribute>();

            foreach (KeyValuePair<Type, GMTableAttribute> kp in pReflectionCache)
            {
                SORTtabAttrs.Add(kp.Value.Priority, kp.Value);
            }


            IDbCommand com = pConnection.CreateCommand();
            com.Connection = pConnection;
            StringBuilder sB = new StringBuilder();

            com.CommandText = "USE master;";
            com.ExecuteNonQuery();
            try
            {
                com.CommandText = String.Format(@"DROP DATABASE {0};", this.Database);
                com.ExecuteNonQuery();
            }
            catch (Exception)
            {

            }
            com.CommandText = String.Format(@"CREATE DATABASE {0};", this.Database);
            com.ExecuteNonQuery();
            sB.AppendFormat(@"USE {0};
", this.Database);
            sB.AppendFormat(@"
CREATE TABLE {0}.tablesync (
  tablename varchar(255) NOT NULL default '',
  modifiedticks bigint(20) NOT NULL default '00000000000000000000',
  PRIMARY KEY  (tablename)
);

", Database);



            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                sB.AppendFormat(@"
INSERT INTO {0}.tablesync VALUES ('{1}','0');
", Database,tableAttr.TableName);
                sB.AppendFormat(@"
CREATE TABLE  {0}.{1} (", Database, tableAttr.TableName);
                foreach (KeyValuePair<FieldInfo, GMColumnAttribute> kp in tableAttr.Columns)
                {
                    if (kp.Value.IsDatabasedColumn)
                    {
                        sB.AppendFormat(@"
    {0} {1} {2} {3},", kp.Value.ColumnName, kp.Value.DatabaseTypeID, (kp.Value.AllowNull ? "" : "NOT NULL"), (kp.Value.IsPrimary ? "IDENTITY" : (kp.Value.AllowNull ? "DEFAULT NULL" : (kp.Value.DatabaseDefaultValue!=null)?"DEFAULT '" + kp.Value.DatabaseDefaultValue+ "'":"")));
                    }
                }
                sB.Append(@"
    PRIMARY KEY (id),");
                foreach (KeyValuePair<FieldInfo, GMColumnAttribute> kp in tableAttr.Columns)
                {
                    if (kp.Value.Key)
                    {
                        sB.AppendFormat(@"
    KEY key_{0}_{1} ({1}),", tableAttr.TableName, kp.Value.ColumnName);

                    }
                }
                foreach (KeyValuePair<FieldInfo, GMColumnAttribute> kp in tableAttr.Columns)
                {
                    if (kp.Value.GMRelationType!=null && kp.Value.IsDatabasedColumn)
                    {
                        sB.AppendFormat(@"
    CONSTRAINT {0}_fkr_{1}
        FOREIGN KEY ({1}_id) REFERENCES {1} (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", tableAttr.TableName, kp.Value.GMRelationAttr.TableName);

                    }
                }
                sB.Remove(sB.Length - 1, 1);
                /*
  id int(10) NOT NULL IDENTITY,
  datecreated bigint(20) NOT NULL default '0',
  datemodified bigint(20) NOT NULL default '0',
  styles_id int(10) NOT NULL default '0000000000',
  PRIMARY KEY  (id),
  KEY plus_ibfk_styles (styles_id),
  CONSTRAINT plus_ibfk_styles FOREIGN KEY (styles_id) REFERENCES styles (id) ON UPDATE RESTRICT,
  CONSTRAINT plus_ibfk_styles FOREIGN KEY (styles_id) REFERENCES styles (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
",);
                 */

                sB.AppendFormat(@"
);
");



            }


            //Then create the link tables

            foreach (GMLinkTable ltbl in pLinkTables)
            {
                sB.AppendFormat(@"
INSERT INTO {0}.tablesync VALUES ('{1}','0');
", Database, ltbl.Name);
                sB.AppendFormat(@"
CREATE TABLE  {0}.{1} (", Database, ltbl.Name);

                sB.AppendFormat(@"
    {0}_id BIGINT(20) NOT NULL DEFAULT 0,", ltbl.Master.TableName );
                sB.AppendFormat(@"
    {0}_id BIGINT(20) NOT NULL DEFAULT 0,", ltbl.Relation.TableName );
                if (ltbl.Index != null)
                {
                    sB.AppendFormat(@"
    {0}_id BIGINT(20) NOT NULL DEFAULT 0,", ltbl.Index.TableName);
                }
                if (ltbl.Parameter != null)
                {
                    
                    sB.AppendFormat(@"
    {0} {1},", ltbl.Parameter.ColumnName, ltbl.Parameter.DatabaseTypeID);
                }
                if (ltbl.Unique&&ltbl.Index!=null) //Only one relation per index.
                {
                    sB.AppendFormat(@"
    PRIMARY KEY ({0},{1}),", ltbl.Master.TableName + "_id", ltbl.Index.TableName + "_id");

                } else if (!ltbl.Duplicates) //More than one of the same relation with the same index.
                {
                    if (ltbl.Index != null)
                    {
                        sB.AppendFormat(@"
    PRIMARY KEY ({0},{1},{2}),", ltbl.Master.TableName + "_id", ltbl.Relation.TableName + "_id", ltbl.Index.TableName + "_id");
                    }
                    else
                    {
                        sB.AppendFormat(@"
    PRIMARY KEY ({0},{1}),", ltbl.Master.TableName + "_id", ltbl.Relation.TableName + "_id");

                    }
                }
                string fkeylinktblname = ltbl.Name;
                if (fkeylinktblname.Length > 32)
                {
                    fkeylinktblname = "";
                    foreach (string str in ltbl.Name.Split('_'))
                    {
                        fkeylinktblname += str.Substring(0, 1);

                    }
                }
                sB.AppendFormat(@"
    CONSTRAINT {0}_fkr_{1}
        FOREIGN KEY ({1}_id) REFERENCES {1} (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Master.TableName);
                sB.AppendFormat(@"
    CONSTRAINT {0}_fkr_{1}
        FOREIGN KEY ({1}_id) REFERENCES {1} (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Relation.TableName);
                if (ltbl.Index != null)
                {

                    sB.AppendFormat(@"
    CONSTRAINT {0}_fkr_{1}
        FOREIGN KEY ({1}_id) REFERENCES {1} (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,", fkeylinktblname, ltbl.Index.TableName);
                }

                sB.Remove(sB.Length - 1, 1);
                sB.AppendFormat(@"
);
");

            }


            Console.WriteLine(sB.ToString());
            Console.WriteLine("Wanna commit this?");
            //Console.ReadLine();
            com.CommandText = sB.ToString();
            com.ExecuteNonQuery();
    
        }
    }
}
